﻿#include "mysqlPool.h"

SqlConnection::SqlConnection(sql::Connection* con, int64_t lasttime):_con(con),_last_oper_time(lasttime)
{
}

mysqlPool::mysqlPool(const std::string& url, const std::string& user, const std::string& pass, const std::string& schema, int poolsize)
	:_url(url),_user(user),_pass(pass),_schema(schema),_b_stop(false),_poolsize(poolsize)
{
	try
	{
		for (size_t i = 0;i < poolsize;++i)
		{
			sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
			auto* con = driver->connect(_url, _user, _pass);
			con->setSchema(_schema);
			//获取当前时间戳
			auto currentTime = std::chrono::system_clock::now().time_since_epoch();
			//将时间戳转换为秒
			std::int64_t timestamp = std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
			_pool.push(std::make_unique<SqlConnection>(con, timestamp));
		}
		//_check_thread = std::thread([this]() { //每隔一段时间检查链接是否失效
		//	while (!_b_stop)
		//	{
		//		checkConnection();
		//		std::this_thread::sleep_for(std::chrono::seconds(60));
		//	}
		//	});
		//_check_thread.detach(); 
	}
	catch (sql::SQLException& e)
	{
		std::cerr << "mysql pool init failed:"<<e.what() << std::endl;
	}
}

void mysqlPool::checkConnection()
{
	std::lock_guard<std::mutex> lock(_mtx);
	auto poolsize = _pool.size();
	auto currentTime = std::chrono::system_clock::now().time_since_epoch();
	
	std::int64_t  timestamp = std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
	for (size_t i = 0;i < poolsize;++i)
	{
		auto con = std::move(_pool.front());
		_pool.pop();

		Defer([this, &con]() {
			_pool.push(std::move(con));
			});

		if (timestamp - con->_last_oper_time < 5)
		{
			continue;
		}

		try
		{
			std::unique_ptr<sql::Statement> stmt(con->_con->createStatement());
			stmt->executeQuery("SELECT 1");
			con->_last_oper_time = timestamp;
			std::cout << "execute time alive query ,cur is " << timestamp << std::endl;

		}
		catch (sql::SQLException& e)
		{
			std::cout << "Error Keep Aliving connection:" << e.what() << std::endl;
			//重新创建链接替换
			sql::mysql::MySQL_Driver* driver = sql::mysql::get_driver_instance();
			auto* newcon = driver->connect(_url, _user, _pass);
			newcon->setSchema(_schema);
			con->_con.reset(newcon);
			con->_last_oper_time = timestamp;
		}
	}
}
std::unique_ptr<SqlConnection> mysqlPool::getConnection()
{
	std::unique_lock<std::mutex> lock(_mtx);
	_condvar.wait(lock, [this]() {
		if (_b_stop)
		{
			return true;
		}
		return !_pool.empty();
		});
	if (_b_stop)
	{
		return nullptr;
	}
	auto con = std::move(_pool.front());
	_pool.pop();
	return con;
}

void mysqlPool::returnConnection(std::unique_ptr<SqlConnection> con)
{
	std::unique_lock<std::mutex> lock(_mtx);
	if (_b_stop)
	{
		return;
	}
	_pool.push(std::move(con));
	_condvar.notify_one();
}

void mysqlPool::Close()
{
	_b_stop = true;
	_condvar.notify_all();
}

mysqlPool::~mysqlPool()
{
	std::unique_lock<std::mutex> lock(_mtx);
	while (!_pool.empty())
	{
		_pool.pop();
	}
}

mysqlDao::mysqlDao()
{
	auto& config = configMgr::GetInst();
	const auto& host = config["mysql"]["host"];
	const auto& port = config["mysql"]["port"];
	const auto& pwd = config["mysql"]["passwd"];
	const auto& user = config["mysql"]["user"];
	const auto& schema = config["mysql"]["schema"];
	_pool.reset(new mysqlPool(host + ":" + port, user, pwd, schema, 5));
}

mysqlDao::~mysqlDao()
{
	_pool->Close();
}

std::shared_ptr<UserInfo> mysqlDao::GetUser(int uid)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return nullptr;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});

	try {
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("SELECT * FROM user WHERE uid = ?"));
		pstmt->setInt(1, uid); 

		std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
		std::shared_ptr<UserInfo> user_ptr = nullptr;
		while (res->next()) {
			user_ptr.reset(new UserInfo);
			user_ptr->pwd = res->getString("pwd");
			user_ptr->email = res->getString("email");
			user_ptr->name = res->getString("name");
			user_ptr->nick = res->getString("nick");
			user_ptr->desc = res->getString("desc");
			user_ptr->sex = res->getInt("sex");
			user_ptr->icon = res->getString("icon");
			user_ptr->uid = uid;
			break;
		}
		return user_ptr;
	}
	catch (sql::SQLException& e) {
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return nullptr;
	}
}

std::shared_ptr<UserInfo> mysqlDao::GetUser(std::string name)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return nullptr;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});

	try {
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("SELECT * FROM user WHERE name = ?"));
		pstmt->setString(1, name); 

		std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
		std::shared_ptr<UserInfo> user_ptr = nullptr;
		while (res->next()) {
			user_ptr.reset(new UserInfo);
			user_ptr->pwd = res->getString("pwd");
			user_ptr->email = res->getString("email");
			user_ptr->name = res->getString("name");
			user_ptr->nick = res->getString("nick");
			user_ptr->desc = res->getString("desc");
			user_ptr->sex = res->getInt("sex");
			user_ptr->uid = res->getInt("uid");
			break;
		}
		return user_ptr;
	}
	catch (sql::SQLException& e) {
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return nullptr;
	}
}

int mysqlDao::RegUser(const std::string& name, const std::string& email, const std::string& pwd)
{
	auto con = _pool->getConnection();
	try {
		if (con == nullptr)
		{
			return -1;
		}
		//准备调用存储过程
		std::unique_ptr<sql::PreparedStatement> stmt(con->_con->prepareStatement("CALL reg_user(?,?,?,@result)"));
		//设置输入参数
		stmt->setString(1, name);
		stmt->setString(2, email);
		stmt->setString(3, pwd);
		
		//执行存储过程
		stmt->execute();

		std::unique_ptr<sql::Statement> stmtRes(con->_con->createStatement());
		std::unique_ptr<sql::ResultSet> res(stmtRes->executeQuery("SELECT @result AS result"));
		if (res->next())
		{
			int result = res->getInt("result");
			std::cout << "result:" << result << std::endl;
			_pool->returnConnection(std::move(con));
			return result;
		}
		_pool->returnConnection(std::move(con));
		return -1;
	}
	catch (sql::SQLException& e)
	{
		_pool->returnConnection(std::move(con));
		std::cerr << "SQL Exception:" << e.what();
		return -1;
	}
}

bool mysqlDao::CheckEmail(const std::string& user, const std::string& email)
{
	auto con = _pool->getConnection();
	try
	{
		if (con == nullptr)
		{
			return false;
		}

		//准备查询语句
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("SELECT email FROM user WHERE name =?"));
		//绑定参数
		pstmt->setString(1, user);
		//执行查询
		std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

		while (res->next())
		{
			std::cout << "Check Email:" << res->getString("email") << std::endl;
			if (email != res->getString("email"))
			{
				_pool->returnConnection(std::move(con));
				return false;
			}
			_pool->returnConnection(std::move(con));
			return true;
		}
	}
	catch (sql::SQLException& e)
	{
		_pool->returnConnection(std::move(con));
		std::cerr << "SQLException:" << e.what() << std::endl;
	}
	
}

bool mysqlDao::UpdatePwd(const std::string& name, const std::string& pwd)
{
	auto con = _pool->getConnection();
	try
	{
		if (con == nullptr)
		{
			return false;
		}

		//准备查询语句
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("UPDATE user SET pwd = ? WHERE name = ?"));
		//绑定参数
		pstmt->setString(1, pwd);
		pstmt->setString(2, name);
		//执行更新
		int updatecount = pstmt->executeUpdate();
		std::cout << "updateRows :" << updatecount << std::endl;
		_pool->returnConnection(std::move(con));
		return true;
	}
	catch (sql::SQLException& e)
	{
		_pool->returnConnection(std::move(con));
		std::cerr << "SQLException:" << e.what() << std::endl;
	}
}

bool mysqlDao::CheckPwd(const std::string& user, const std::string& pwd,UserInfo& userinfo)
{
	auto con = _pool->getConnection();
	try {
		if (con == nullptr)
		{
			std::cerr << "null connection" << std::endl;
			return false;
		}
		Defer defer([this,&con]() {
			_pool->returnConnection(std::move(con));
			});
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("SELECT * FROM user WHERE user = ?"));
		pstmt->setString(1, user);
		std::unique_ptr<sql::ResultSet> result(pstmt->executeQuery());
		std::string origin_pwd = "";
		while (result->next())
		{
			origin_pwd = result->getString("pwd");

			//输出查询到的密码
			std::cout << "Password:" << origin_pwd << std::endl;
			break;
		}

		if (pwd != origin_pwd)
		{
			return false;
		}

		userinfo.email = result->getString("email");
		userinfo.pwd = origin_pwd;
		userinfo.uid = result->getInt("uid");
		userinfo.user = result->getString("name");
		return true;
		
	}
	catch(sql::SQLException& e)
	{
		std::cerr<< "SQLException:" << e.what() << std::endl;
	}
}

bool mysqlDao::AddFriendApply(const int& from, const int& to)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return false;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});

	try {
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("INSERT INTO friend_apply (from_uid, to_uid) values (?,?) "
			"ON DUPLICATE KEY UPDATE from_uid = from_uid, to_uid = to_uid "));
		pstmt->setInt(1, from);
		pstmt->setInt(2, to);
		//执行更新
		int rowAffected = pstmt->executeUpdate();
		if (rowAffected < 0)
		{
			return false;
		}
		return true;
	}
	catch (sql::SQLException e)
	{
		std::cerr << "SQLException:" << e.what() << std::endl;
		return false;
	}

}

bool mysqlDao::GetApplyList(const int& uid, std::vector<std::shared_ptr<ApplyInfo>>& apply_list, int offset, int limit)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return false;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});


	try {
		// 准备查询语句，根据起始id和限制条数返回列表
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("select apply.from_uid, apply.status, user.name, "
			"user.nick, user.sex from friend_apply as apply join user on apply.from_uid = user.uid where apply.to_uid = ? "
			"and apply.id > ? order by apply.id ASC LIMIT ? "));

		pstmt->setInt(1, uid); // 将uid替换为要查询的uid
		pstmt->setInt(2, offset); // 起始id
		pstmt->setInt(3, limit); // 偏移量
		//	执行查询
		std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
		// 遍历结果集
		while (res->next()) {
			auto name = res->getString("name");
			auto uid = res->getInt("from_uid");
			auto status = res->getInt("status");
			auto nick = res->getString("nick");
			auto sex = res->getInt("sex");
			auto apply_ptr = std::make_shared<ApplyInfo>(uid, name, "", "", nick, sex, status);
			apply_list.push_back(apply_ptr);
		}
		return true;
	}
	catch (sql::SQLException& e) {
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return false;
	}
}

bool mysqlDao::AuthFriendApply(const int& uid, const int& touid)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return false;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});

	try {
		// 准备SQL语句
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("UPDATE friend_apply SET status = 1 "
			"WHERE from_uid = ? AND to_uid = ?"));
		// 反过来的申请时uid，验证时touid
		pstmt->setInt(1, touid); // from id
		pstmt->setInt(2, uid);
		// 执行更新
		int rowAffected = pstmt->executeUpdate();
		if (rowAffected < 0) {
			return false;
		}
		return true;
	}
	catch (sql::SQLException& e) {
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return false;
	}


	return true;
}

bool mysqlDao::GetFriendList(const int& self_id, std::vector<std::shared_ptr<UserInfo>>& user_list)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return false;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});


	try {
		// 准备SQL语句，根据起始id和限制条数返回列表
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("select * from friend where self_id = ? "));

		pstmt->setInt(1, self_id); // 将uid修改为要查询的id

		//	执行查询
		std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
		// 遍历结果集
		while (res->next()) {
			auto friend_id = res->getInt("friend_id");
			auto back = res->getString("back");
			// 再次查询friend_id 对应的信息
			auto user_info = GetUser(friend_id);
			user_info->uid = friend_id;
			if (user_info == nullptr) {
				continue;
			}

			user_info->nick = user_info->name;
			user_list.push_back(user_info);
		}
		return true;
	}
	catch (sql::SQLException& e) {
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return false;
	}

	return true;
}

bool mysqlDao::AddFriend(const int& uid, const int& touid, const std::string& back_name)
{
	auto con = _pool->getConnection();
	if (con == nullptr) {
		return false;
	}

	Defer defer([this, &con]() {
		_pool->returnConnection(std::move(con));
		});

	try {

		//开始事务
		con->_con->setAutoCommit(false);

		// 准备第一个SQL语句，插入认证方好友数据
		std::unique_ptr<sql::PreparedStatement> pstmt(con->_con->prepareStatement("INSERT IGNORE INTO friend(self_id, friend_id, back) "
			"VALUES (?, ?, ?) "
		));
		//反过来的申请时uid，验证时touid
		pstmt->setInt(1, uid); // from id
		pstmt->setInt(2, touid);
		pstmt->setString(3, back_name);
		// 执行更新
		int rowAffected = pstmt->executeUpdate();
		if (rowAffected < 0) {
			con->_con->rollback();
			return false;
		}

		//准备第二个SQL语句，插入申请方好友数据
		std::unique_ptr<sql::PreparedStatement> pstmt2(con->_con->prepareStatement("INSERT IGNORE INTO friend(self_id, friend_id, back) "
			"VALUES (?, ?, ?) "
		));

		pstmt2->setInt(1, touid); // from id
		pstmt2->setInt(2, uid);
		pstmt2->setString(3, "");
		// 执行更新
		int rowAffected2 = pstmt2->executeUpdate();
		if (rowAffected2 < 0) {
			con->_con->rollback();
			return false;
		}

		// 提交
		con->_con->commit();
		std::cout << "addfriend insert friends success" << std::endl;

		return true;
	}
	catch (sql::SQLException& e) {
		// ����������󣬻ع�����
		if (con) {
			con->_con->rollback();
		}
		std::cerr << "SQLException: " << e.what();
		std::cerr << " (MySQL error code: " << e.getErrorCode();
		std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
		return false;
	}


	return true;
}
